
package com.kaciula.letsdoit.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDoneException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class WastesDbAdapter {

    private static final String DATABASE_NAME = "data.db";

    private static final String DATABASE_TABLE = "locations";

    private static final int DATABASE_VERSION = 1;

    /* Table fields */
    public static final String KEY_ROWID = "_id";

    public static final String KEY_LAT = "lat";

    public static final String KEY_LONG = "long";

    public static final String KEY_LENGTH = "length";

    public static final String KEY_BREADTH = "breadth";

    public static final String KEY_HEIGHT = "height";

    public static final String KEY_DISPERSED = "dispersed";

    public static final String KEY_EQUIVALENT = "equivalent";

    public static final String KEY_COMPOSITION_NOT_RECYCLABLE = "not_recyclable";

    public static final String KEY_COMPOSITION_GLASS = "glass";

    public static final String KEY_COMPOSITION_METAL = "metal";

    public static final String KEY_COMPOSITION_PLASTIC = "plastic";

    public static final String KEY_COMPOSITION_PAPER = "paper";

    public static final String KEY_COMPOSITION_TIRE = "tire";

    public static final String KEY_COMPOSITION_BIG_VOLUME = "big_volume";

    public static final String KEY_COMPOSITION_CONSTRUCTION_MATERIAL = "construction_material";

    public static final String KEY_DESCRIPTION = "description";

    /**
     * Database creation SQL statement
     */
    private static final String DATABASE_CREATE = "create table " + DATABASE_TABLE + " ("
            + KEY_ROWID + " integer primary key, " + KEY_LAT + " real not null, "
            + KEY_LONG + " real not null, " + KEY_LENGTH + " integer not null, " + KEY_BREADTH
            + " integer not null, " + KEY_HEIGHT + " integer not null, " + KEY_DISPERSED
            + " integer not null default 0, " + KEY_EQUIVALENT + " integer not null, "
            + KEY_COMPOSITION_NOT_RECYCLABLE + " integer not null default 0, "
            + KEY_COMPOSITION_GLASS + " integer not null default 0, " + KEY_COMPOSITION_METAL
            + " integer not null default 0, " + KEY_COMPOSITION_PLASTIC
            + " integer not null default 0, " + KEY_COMPOSITION_PAPER
            + " integer not null default 0, " + KEY_COMPOSITION_TIRE
            + " integer not null default 0, " + KEY_COMPOSITION_BIG_VOLUME
            + " integer not null default 0, " + KEY_COMPOSITION_CONSTRUCTION_MATERIAL
            + " integer not null default 0, " + KEY_DESCRIPTION + " text);";

    private DatabaseHelper mDbHelper;

    private SQLiteDatabase mDb;

    private static final String TAG = "WastesDbAdapter";

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion
                    + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public WastesDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the database. If it cannot be opened, try to create a new instance
     * of the database. If it cannot be created, throw an exception to signal
     * the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public WastesDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }
    
    /**
     * Return a Cursor over the list of all wastes in the database
     * 
     * @return Cursor over all wastes
     */
    public Cursor fetchAllWastes() {
        return mDb.query(DATABASE_TABLE, new String[] {
            KEY_ROWID
        }, null, null, null, null, KEY_ROWID + " ASC");
    }

    public long fetchFutureId() throws SQLException, SQLiteDoneException {
        String sql = String.format("SELECT COALESCE(MAX(%s)+1, 0) FROM %s", KEY_ROWID,
                DATABASE_TABLE);
        SQLiteStatement statement = mDb.compileStatement(sql);

        long id = statement.simpleQueryForLong();

        // if there are no rows, the first id will be 1
        if (id <= 0) {
            id = 1;
        }

        return id;
    }

    /**
     * Return a Cursor positioned at the wastes that matches the given rowId
     * 
     * @param rowId id of waste to retrieve
     * @return Cursor positioned to matching waste, if found
     */
    public Cursor fetchWaste(long rowId) {
        Cursor cursor = mDb.query(true, DATABASE_TABLE, null, KEY_ROWID + "=" + rowId, null, null,
                null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    /**
     * Return a Cursor positioned at the wastes that match the like_pattern
     * 
     * @param like_pattern query string
     * @return Cursor that contains all wastes with name similar to
     *         like_pattern
     * @throws SQLException
     */
    public Cursor fetchWastes(String like_pattern) {
        Cursor cursor = mDb.query(true, DATABASE_TABLE, new String[] {
            KEY_ROWID
        }, KEY_ROWID + " LIKE " + like_pattern, null, null, null, KEY_ROWID + " ASC", null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    /**
     * Create a new waste using the values provided. If the waste is
     * successfully created return the new rowId for that waste, otherwise
     * return a -1 to indicate failure.
     * 
     * @param args values for the fields
     * @return true if successful, false otherwise
     */
    public boolean createWaste(ContentValues args) {
        return mDb.insert(DATABASE_TABLE, null, args) >= 0;
    }

    /**
     * Delete the waste with the given rowId
     * 
     * @param rowId id of waste to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteWaste(long rowId) {
        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Update the waste using the details provided. The waste to be
     * updated is specified using the rowId, and it is altered to use the name,
     * latitude and longitude provided
     * 
     * @param rowId id of waste to update
     * @param args values changed
     * @return true if the waste was successfully updated, false otherwise
     */
    public boolean updateWaste(long rowId, ContentValues args) {
        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}
